﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace UDS.DB.Lib
{
    internal class DBConnection : IDisposable
    {
        private readonly SqlConnection _sqlConnection;
        private SqlTransaction _sqlTransaction;

        /// <summary>
        /// Constructor.
        /// </summary>
        internal DBConnection(string cs)
        {
            if (string.IsNullOrEmpty(cs))
            {
                throw new ArgumentNullException("cs");
            }

            _sqlConnection = new SqlConnection(cs);
        }

        public void BeginTransaction()
        {
            _sqlTransaction = _sqlConnection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            _sqlTransaction.Commit();
            _sqlTransaction = null;
        }

        public void RollbackTransaction()
        {
            _sqlTransaction.Rollback();
            _sqlTransaction = null;
        }

        /// <summary>
        /// Opens the database connection.
        /// </summary>
        public void Open()
        {
            _sqlConnection.Open();
        }

        /// <summary>
        /// Close the database connection.
        /// </summary>
        public void Close()
        {
            if (_sqlTransaction != null && _sqlTransaction.Connection != null)
            {
                _sqlTransaction.Rollback();
                _sqlTransaction = null;
            }
            _sqlConnection.Close();
        }

        /// <summary>
        /// Execute the specified SQL on the current SQL connection.
        /// </summary>
        public void ExecuteSQL(string sql, IList<SqlParameter> parameters)
        {
            using (var sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection = _sqlConnection;
                if (_sqlTransaction != null)
                {
                    sqlCommand.Transaction = _sqlTransaction;
                }
                sqlCommand.CommandText = sql;
                sqlCommand.CommandType = CommandType.Text;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        sqlCommand.Parameters.Add(p);
                    }
                }

                sqlCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Execute the specified SQL on the current SQL connection, and returns a SqlDataReader object to fetch the result set.
        /// </summary>
        public SqlDataReader ExecuteReader(string sql, IList<SqlParameter> parameters)
        {
            using (var sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection = _sqlConnection;
                if (_sqlTransaction != null)
                {
                    sqlCommand.Transaction = _sqlTransaction;
                }
                sqlCommand.CommandText = sql;
                sqlCommand.CommandType = CommandType.Text;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        sqlCommand.Parameters.Add(p);
                    }
                }

                return sqlCommand.ExecuteReader();
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            Close();
        }

        #endregion
    }
}
